using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
namespace Test {
	class DataAccessLayer {

		public static string CsvColumn(DataTable tab,string colName,bool quote) {
			StringBuilder sb=new StringBuilder();
			List<string> vals=new List<string>(tab.Rows.Count);
			foreach(DataRow row in tab.Rows)
				//if(!vals.Contains(row[colName].ToString()))
				vals.Add(row[colName].ToString());
			if(quote)
				return string.Concat("'",string.Join("','",vals.ToArray()),"'");
			else
				return string.Join(",",vals.ToArray());
		}
		string _cs;
		public DataAccessLayer() : this("server=(local);integrated security=sspi;connection timeout=1") { }
		public DataAccessLayer(params string[] args) {
			if(args.Length==2)
				_cs=string.Format("server={0};database={1};integrated security=sspi;connection timeout=1",args);
			else
				throw new Exception();
		}
		public DataAccessLayer(string cs) { _cs=cs; }
		public DataTable Query(string sql,params object[] args) {
			string qry=string.Format(sql,args);
			//Console.WriteLine(qry);
			DataTable ret=new DataTable();
			using(SqlDataAdapter adp=new SqlDataAdapter(qry,_cs))
			{
				adp.SelectCommand.CommandTimeout=0;
				adp.Fill(ret);
			}
			return ret;
		}
		public int NonQry(string sql,params object[] args) {
			string qry=sql;
			if(args.Length!=0)
				qry=string.Format(sql,args);
			int ret=-1;
			using(SqlConnection con=new SqlConnection(_cs))
			using(SqlCommand cmd=new SqlCommand(qry,con))
			{
				con.Open();
				ret=cmd.ExecuteNonQuery();
				con.Close();
			}
			return ret;
		}
		public object Scalar(string sql,params object[] args) {
			string qry=string.Format(sql,args);
			object ret=null;
			using(SqlConnection con=new SqlConnection(_cs))
			using(SqlCommand cmd=new SqlCommand(qry,con))
			{
				con.Open();
				ret=cmd.ExecuteScalar();
				con.Close();
			}
			return ret;
		}
	}
	class JobFailCheck {
		static void MainX() {
			List<string> lst=new List<string>();
			lst.AddRange(Mixer.Combine("pon-wpsq{0}-db","01,03,06,07,08,09,12,13,16"));
			lst.Add("pon-wpsq07");
			lst.AddRange(Mixer.Combine("pon-wpsq21-db{0}\\in{0}","01,02,03,04,05,06,07,08,09,10,11,12"));
			string sql=@"SELECT j.name,convert(char(19),CONVERT(DATETIME,RTRIM(run_date))+((run_time/10000*3600)+((run_time%10000)/100*60)+(run_time%10000)%100)/(23.999999*3600),20)Start_DateTime,message,step_name,run_duration from msdb..sysjobhistory h JOIN msdb..sysjobs j ON h.job_id = j.job_id 
WHERE h.run_status=0 and run_date>convert(char(8),getdate()-3,112)";
			DataTable master=new DataTable();
			master.Columns.Add("server",typeof(string));
			master.Columns.Add("sp",typeof(string));
			master.Columns.Add("failTime",typeof(DateTime));
			master.Columns.Add("msg",typeof(string));
			master.Columns.Add("step_name",typeof(string));
			master.Columns.Add("run_duration",typeof(int));
			foreach(string srv in lst)
			{
				DataAccessLayer dal=new DataAccessLayer(srv,"master");
				try
				{
					DataTable tab=dal.Query(sql);
					Console.WriteLine(srv);
					foreach(DataRow row in tab.Rows)
						master.Rows.Add(srv,row[0],row[1],row[2],row[3],row[4]);
				}
				catch(Exception ex) { Console.WriteLine(ex.Message); }
			}
			DataAccessLayer local=new DataAccessLayer();
			//local.NonQry("truncate table jobFail");
			foreach(DataRow row in master.Rows)
				if(local.NonQry("update jobFail set lastcheck=getdate(),dur={0} where sp='{1}' and failed=convert(datetime,'{2:yyyy-MM-dd HH:mm:ss}',20)",row[5],row[1],row[2])==0)
					local.NonQry("insert into jobFail(srv,sp,failed,msg,step,dur)values('{0}','{1}',convert(datetime,'{2:yyyy-MM-dd HH:mm:ss}',20),'{3}','{4}',{5})",row[0],row[1],row[2],row[3].ToString().Replace("'","''"),row[4],row[5]);
		}
	}
}
